// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Net;
using System.Text;
using Microsoft.Win32;

// bugbug
// 2: get correct column type for all DB types
// 3: faster FK:PK relationships for columns.
// 3: get system/all tables/views/stored procedures
// 4: get table/view/stored procedure descriptions, faster get column descriptions.
// 4: get stored procedures variables (type & description)
// this may help - http://msdn.microsoft.com/en-us/library/ms254969(VS.80).aspx

namespace net.windward.utils.ado.SqlServer
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This calls is the vendor implementation for Sql Server.
	/// </summary>
	public class WrSqlServerVendor : WrVendor
	{

		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "System.Data.SqlClient";

		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "Sql Server";

		/// <summary>
		/// Creates a SqlServer vendor object.
		/// </summary>
		public WrSqlServerVendor()
			: base(FactoryName, "The standard Sql Server connector", FactoryClass)
		{
			syntax = new WrSqlServerSyntax();
		}

		/// <summary>
		/// Creates a Sql Server vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrSqlServerVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
			syntax = new WrSqlServerSyntax();
		}

		#region Properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get { return true; }
		}

		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return true; }
		}

		/// <summary>
		/// True if can query metadata from the database.
		/// </summary>
		public override bool CanQueryMetadata
		{
			get { return true; }
		}

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get
			{
				return AdminFilename() != null;
			}
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return true; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return true; }
		}

		#endregion

		#region WrProvider methods

		/// <summary>
		/// A connection string for a database on a server.
		/// </summary>
		/// <param name="server">The server to access (ex: localhost).</param>
		/// <param name="database">The database to connect to.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="showPassword">True to include the password, false to add the password as *****.</param>
		/// <returns>The connection string for this database on this server.</returns>
		public override string ConnectionString(string server, string database, WrCredentials credentials, bool showPassword)
		{
			return ConnectionString(providerFactory, server, database, credentials, showPassword);
		}

		internal static string ConnectionString(DbProviderFactory provider, string server, string database, WrCredentials credentials, bool showPassword)
		{

			DbConnectionStringBuilder connStrBuilder = provider.CreateConnectionStringBuilder();
			if (!string.IsNullOrEmpty(server))
			{
				connStrBuilder.Add("Data Source", server);
				IPAddress addr;
				if (IPAddress.TryParse(server, out addr))
					connStrBuilder.Add("Network Library", "DBMSSOCN");
			}
			if (!string.IsNullOrEmpty(database))
				connStrBuilder.Add("Initial Catalog", database);
			if (credentials.UseWindowsIdentity)
				connStrBuilder.Add("Integrated Security", "SSPI");
			else
			{
				if (!string.IsNullOrEmpty(credentials.Username))
					connStrBuilder.Add("User ID", credentials.FullName);
				if (!string.IsNullOrEmpty(credentials.Password))
					connStrBuilder.Add("Password", showPassword ? credentials.Password : "*****");
			}
			return connStrBuilder.ConnectionString;
		}

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{

			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
			if (builder.IntegratedSecurity)
				return new WrConnectionParams(builder.DataSource, builder.InitialCatalog);
			return new WrConnectionParams(builder.DataSource, builder.InitialCatalog, builder.UserID, builder.Password);
		}

		/// <summary>
		/// All servers from this vendor on the network. This may not get all due to routers not passing
		/// on a broadcast or delays in some servers responding.
		/// </summary>
		/// <exception cref="WrDbException">Thrown if this provider does not support enumerate servers.</exception>
		/// <returns>All servers found on the sub-net.</returns>
		public override IWrServer[] GetServers()
		{
			ArrayList rtn = new ArrayList();
			DbDataSourceEnumerator dsEnum = providerFactory.CreateDataSourceEnumerator();
			if (dsEnum != null)
			{
				DataTable servers = dsEnum.GetDataSources();
				int ordServer = servers.Columns.IndexOf("ServerName");
				int ordInstance = servers.Columns.IndexOf("InstanceName");
				foreach (DataRow row in servers.Rows)
				{
					string server = (string) row[ordServer];
					string instance = row.IsNull(ordInstance) ? null : (string) row[ordInstance];
					if (!string.IsNullOrEmpty(instance))
						server += "\\" + instance;
					rtn.Add(new WrSqlServerServer(providerFactory, server));
				}
			}

			rtn.Sort();
			return (WrSqlServerServer[])rtn.ToArray(typeof(WrSqlServerServer));
		}

		/// <summary>
		/// Run a DDL script against a server (specific install on that box).
		/// </summary>
		/// <param name="server">The server to access.</param>
		/// <param name="database">The database to run the script against (ex: Northwind).</param>
		/// <param name="script">The DDL script.</param>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <exception cref="WrDbException">Thrown if this provider does not support running a script.</exception>
		/// <returns>True if successful.</returns>
		public override bool RunScript(string server, string database, StreamReader script, WrCredentials credentials)
		{

			// read the script
			string full = CleanUpScript(script);

			using (IDbConnection sc = providerFactory.CreateConnection())
			{
				sc.ConnectionString = ConnectionString(server, database, credentials, true);
				_RunScript(sc, full);
			}
			return true;

			/* how to do it from the command line
						string exe = Path.Combine(GetExe("90"), "osql.exe");
						if (!File.Exists(exe))
						{
							exe = Path.Combine(GetExe("80"), "osql.exe");
							if (!File.Exists(exe))
								exe = Path.GetFullPath("C:\\Program Files\\Microsoft SQL Server\\90\\Tools\\Binn\\osql.exe");
						}

						string cmd = "-S " + server + " ";
						if (username == null)
							cmd += "-E ";
						else
							cmd += "-U " + username + " -P " + password + " ";
						cmd += "-i \"" + script + "\"";

						return RunProc.run("Database Creator", exe, cmd, true);
			 */
		}

		/// <summary>
		/// Run a DDL script against a server (specific install on that box).
		/// </summary>
		/// <param name="connStr">The connection string to connect with.</param>
		/// <param name="script">The DDL script.</param>
		/// <exception cref="WrDbException">Thrown if this provider does not support running a script.</exception>
		/// <returns>True if successful.</returns>
		public override bool RunScript(string connStr, StreamReader script)
		{

			// read the script
			string full = CleanUpScript(script);

			using (IDbConnection sc = providerFactory.CreateConnection())
			{
				sc.ConnectionString = connStr;
				_RunScript(sc, full);
			}
			return true;
		}

		private static void _RunScript(IDbConnection sc, string full)
		{

			sc.Open();
			using (IDbCommand cmd = sc.CreateCommand())
			{
				cmd.CommandType = CommandType.Text;
				int start = 0;
				// looking for whitespace GO whitespace
				int end = start;
				int mode = 0; // 0=other; 1=whitespace; 2=G; 3=O
				while (end < full.Length)
				{
					switch (full[end])
					{
						case '\r':
						case '\n':
						case '\t':
						case ' ':
							// do we have it?
							if (mode == 3)
							{
								ExecuteStatement(cmd, full.Substring(start, end - 3 - start).Trim());
								start = end + 1;
							}
							mode = 1;
							break;
						case 'G':
						case 'g':
							mode = mode == 1 ? 2 : 0;
							break;
						case 'O':
						case 'o':
							mode = mode == 2 ? 3 : 0;
							break;
						default:
							mode = 0;
							break;
					}
					end++;
				}
			}
		}

		private static string CleanUpScript(TextReader script)
		{
			StringBuilder schema = new StringBuilder();
			string line;
			while ((line = script.ReadLine()) != null)
				schema.Append(line + '\n');
			// no trim - need the final \n after the last GO
			return schema.ToString();
		}

		private static void ExecuteStatement(IDbCommand cmd, string statement)
		{
			if (statement.Length == 0)
				return;
			try
			{
				cmd.CommandText = statement;
				cmd.ExecuteNonQuery();
			} 
			catch (Exception ex)
			{
				throw new WrDbException(string.Format("Exception: {0}\nExecuting script statement: {1}", ex.Message, statement));
			}
		}

		/// <summary>
		/// Launch the database admin tool.
		/// </summary>
		/// <returns>True if launched successfully.</returns>
		public override bool RunAdministrator()
		{
			Process proc = Process.Start(AdminFilename());
			return (proc != null) && (!proc.HasExited);
		}

		private static string AdminFilename()
		{
			string exe = Path.Combine(GetExe("90"), "VSShell\\Common7\\IDE\\SqlWb.exe");
			if (File.Exists(exe))
				return exe;
			exe = Path.Combine(GetExe("90"), "VSShell\\Common7\\IDE\\ssmsee.exe");
			if (File.Exists(exe))
				return exe;
			exe = Path.Combine(GetExe("90"), "SQL Server Enterprise Manager.MSC");
			if (File.Exists(exe))
				return exe;
			exe = Path.Combine(GetExe("80"), "SQL Server Enterprise Manager.MSC");
			if (File.Exists(exe))
				return exe;
			return null;
		}

		// get the base path for a version of Sql Server
		private static string GetExe(string ver)
		{
			RegistryKey key = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\" + ver + "\\Tools\\ClientSetup");
			if (key == null)
				return null;
			string exe = (string)key.GetValue("SQLPath");
			if (exe == null)
				return null;
			exe = Path.GetFullPath(Path.Combine(exe, "Binn"));
			key.Close();
			return exe;
		}

		#endregion

		#region DbProvider methods

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
			return new WrSqlServerCommand(providerFactory);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
			return new WrSqlServerServer(providerFactory, server);
		}

		#endregion

	}
}
